ALTER   function fn_get_process_flow_from_pathString ( @path NVARCHAR (1000) )
RETURNS @Results TABLE( ProcessStep_ID INT, PS_Type varchar(50), Ps_Type_Name varchar(50) )
AS 
BEGIN
	DECLARE @Process_Path TABLE ( Process_Step_ID INT, PS_Type varchar(50), Ps_Type_Name varchar(50), Ps_Type_Id int )
	DECLARE @Temp_ProcessStep_Id INT
	DECLARE @idx INT
	SET @idx = 1

	WHILE ( LEN( @path) > 0  )
	BEGIN
		SET @idx = CHARINDEX( ',', @path, @idx )
		IF ( @idx > 0 ) 
		BEGIN
			SET @Temp_ProcessStep_Id = SUBSTRING( @path, 1, @idx-1 )
		    	SET @path = SUBSTRING ( @path, @idx+1, LEN ( @path) )
		END
		ELSE
		BEGIN
			SET @Temp_ProcessStep_Id = SUBSTRING ( @path, 1, LEN( @path) )
		    	SET @path = ''
		END
		--INSERT INTO @Process_Path SELECT PROCESSSTEP_ID, PS_TYPE, PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = @Temp_ProcessStep_Id
		INSERT INTO @Process_Path SELECT PROCESSSTEP_ID, PS_TYPE, PS_TYPE_NAME, 
			CASE 
				WHEN PS_TYPE_NAME = 'FAB' THEN 1
				WHEN PS_TYPE_NAME = 'Wafer Store' THEN 2
				WHEN PS_TYPE_NAME = 'SORT' THEN 3
				WHEN PS_TYPE_NAME = 'Die Bank' THEN 4
				WHEN PS_TYPE_NAME = 'Assembly' THEN 5
				WHEN PS_TYPE_NAME = 'TEST' THEN 6
				WHEN PS_TYPE_NAME = 'Class Store' THEN 7
				WHEN ( PS_TYPE_NAME = 'Mark & Pack' OR PS_TYPE_NAME = 'Mark &amp; Pack' ) THEN 8
				WHEN ( PS_TYPE_NAME = 'Finished Goods' OR PS_TYPE_NAME = 'FG' ) THEN 9
			END PS_TYPE_ID 
		FROM PROCESS_STEP WHERE PROCESSSTEP_ID = @Temp_ProcessStep_Id
	END
	INSERT INTO @Results SELECT Process_Step_ID, PS_Type, Ps_Type_Name FROM @Process_Path ORDER BY Ps_Type_Id
	RETURN 
END


